using System;
using System.Drawing;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Windows.Forms;
using xl = Microsoft.Office.Interop.Excel;
using System.Text;
using System.IO;
using System.Text.RegularExpressions;

//remove the need for these
using System.Runtime.CompilerServices;
using Deloitte.MSExcel;
using Deloitte.Data;
using Deloitte.Windows.Forms;
using Microsoft.VisualBasic.CompilerServices;

namespace Deloitte.Common
{
	/// <summary>
	/// Summary description for Utility.
	/// </summary>
	public class Utility
	{
        
        private static System.Threading.Thread formPatientThread;        
		private static PatientForm frmPatient = new PatientForm();
		
		private void ShowThreadedForms(System.Windows.Forms.Form form)
		{
			form.Show();
		}
 
        //}
        /// <summary>
        /// Sets a predefined color scheme to a DataGridView
        /// </summary>
        /// <param name="dataGridView">DataGridView to set the color scheme for</param>
        public static void SetDataGridViewColorScheme(System.Windows.Forms.DataGridView dataGridView)
        {            
            dataGridView.DefaultCellStyle.BackColor = System.Drawing.Color.GhostWhite;
            dataGridView.DefaultCellStyle.ForeColor = System.Drawing.Color.MidnightBlue;
            dataGridView.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
            dataGridView.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
            dataGridView.DefaultCellStyle.SelectionBackColor = System.Drawing.Color.Red;
            dataGridView.DefaultCellStyle.SelectionForeColor = System.Drawing.Color.MidnightBlue;
            dataGridView.AlternatingRowsDefaultCellStyle = dataGridView.DefaultCellStyle.Clone();
            dataGridView.AlternatingRowsDefaultCellStyle.BackColor = Color.LightGray;
        }
        /// <summary>
        /// Sets a predefined color scheme to a DataGridTableStyle
        /// </summary>
        /// <param name="tableStyle">DataGridTable style to set the color theme for</param>
		public static void SetTableStyleColorScheme(System.Windows.Forms.DataGridTableStyle tableStyle)
		{
			tableStyle.BackColor = System.Drawing.Color.GhostWhite;
			tableStyle.ForeColor = System.Drawing.Color.MidnightBlue;
			tableStyle.GridLineColor = System.Drawing.Color.RoyalBlue;
			tableStyle.HeaderBackColor = System.Drawing.Color.MidnightBlue;
			tableStyle.HeaderFont = new System.Drawing.Font("Tahoma", 8.0f, System.Drawing.FontStyle.Bold);
			tableStyle.HeaderForeColor = System.Drawing.Color.Lavender;
			tableStyle.LinkColor = System.Drawing.Color.Teal;                                                                                
			tableStyle.SelectionBackColor = System.Drawing.Color.Red;
			tableStyle.SelectionForeColor = System.Drawing.Color.MidnightBlue;
			tableStyle.AlternatingBackColor = Color.LightGray;
		}
		/// <summary>
		/// Gets the width of a datatable column to display the longest field in a datagrid
		/// </summary>
		/// <param name="dt">A datatable containing the column</param>
		/// <param name="colName">Name of the column to get the width for</param>
		/// <returns></returns>
		public static int GetDataGridColumnWidth(DataTable dt, string colName)
		{
			try
			{
				string longestString = string.Empty;
				string tmpString = string.Empty;
				foreach(DataRow dr in dt.Rows)
				{
					tmpString = dr[colName].ToString();
					if(tmpString.Length>longestString.Length)longestString=tmpString;
				}
				if (longestString.Length < colName.Length) return (int)(colName.Length);
				else{return (int)(longestString.Length * .8);}
			}
			catch{return 10;}
		}
        /// <summary>
        /// Removes any chars from the string that are not alpha or numeric
        /// </summary>
        /// <param name="aString">String to remove non alpha and numeric chars from</param>
        /// <param name="replaceChar">Character to replace any on alpha and numeric characters with</param>
		public static string RemoveSpecialChars(string aString, char replaceChar)
		{
			char[] myChars = aString.ToCharArray();
			char myChar;
			for(int i = 0;i<myChars.Length; i++)
			{
				myChar = myChars[i];
				if(!System.Char.IsLetterOrDigit(myChar))myChars[i] = replaceChar;
			}
			return new string(myChars);
		}
        public static DataTable CreateDataTable(string SQLCommand, string oleDbConnectionString)
        {
            OleDbConnection myConnection = new OleDbConnection();
            try
            {
                myConnection.ConnectionString = oleDbConnectionString;
                OleDbCommand myCommand = new OleDbCommand();
                myConnection.Open();
                myCommand.Connection = myConnection;
                myCommand.CommandText = SQLCommand;
                OleDbDataAdapter myAdapter = new OleDbDataAdapter();
                myAdapter.SelectCommand = myCommand;
                DataSet myDataSet = new DataSet();
                myAdapter.Fill(myDataSet);
                return myDataSet.Tables[0];
            }
            catch (Exception ex)
            {
                ExceptionHandling.ShowMessageBox(ex);
                return new System.Data.DataTable();
            }
            finally
            {
                if (myConnection.State == ConnectionState.Open) myConnection.Close();
            }
        }
        private static DataTable ParseCsv(string inputString, bool hasHeaders)
        {

            DataTable dt = new DataTable();

            // declare the Regular Expression that will match versus the input string
            Regex re = new Regex("((?<field>[^\",\\r\\n]+)|\"(?<field>([^\"]|\"\")+)\")(,|(?<rowbreak>\\r\\n|\\n|$))");

            ArrayList colArray = new ArrayList();
            ArrayList rowArray = new ArrayList();

            int colCount = 0;
            int maxColCount = 0;
            string rowbreak = "";
            string field = "";

            MatchCollection mc = re.Matches(inputString);

            foreach (Match m in mc)
            {

                // retrieve the field and replace two double-quotes with a single double-quote
                field = m.Result("${field}").Replace("\"\"", "\"");

                rowbreak = m.Result("${rowbreak}");

                if (field.Length > 0)
                {
                    colArray.Add(field);
                    colCount++;
                }

                if (rowbreak.Length > 0)
                {

                    // add the column array to the row Array List
                    rowArray.Add(colArray.ToArray());

                    // create a new Array List to hold the field values
                    colArray = new ArrayList();

                    if (colCount > maxColCount)
                        maxColCount = colCount;

                    colCount = 0;
                }
            }

            if (rowbreak.Length == 0)
            {
                // this is executed when the last line doesn't
                // end with a line break
                rowArray.Add(colArray.ToArray());
                if (colCount > maxColCount)
                    maxColCount = colCount;
            }

          

            // convert the row Array List into an Array object for easier access
            Array ra = rowArray.ToArray();
            for (int i = 0; i < ra.Length; i++)
            {

                // create the columns for the table
                if (i == 0)
                {
                    // convert the column Array List into an Array object for easier access
                    Array headers=null;
                    if (hasHeaders) {  headers = (Array)(ra.GetValue(i)); }
                    for (int c = 0; c < maxColCount; c++)
                    {
                        string colName = string.Empty;
                        if (hasHeaders) { colName = headers.GetValue(c).ToString(); }
                        else { colName = String.Format("col{0:000}", c); }
                        dt.Columns.Add(colName);
                    }
                    if (hasHeaders) { i ++; }
                }
                // create a new DataRow
                DataRow dr = dt.NewRow();

                // convert the column Array List into an Array object for easier access
                Array ca = (Array)(ra.GetValue(i));

                // add each field into the new DataRow
                for (int j = 0; j < ca.Length; j++)
                    dr[j] = ca.GetValue(j);

                // add the new DataRow to the DataTable
                dt.Rows.Add(dr);
            }

            // in case no data was parsed, create a single column
            if (dt.Columns.Count == 0)
                dt.Columns.Add("NoData");

            return dt;
        }
        //todo: fix the data provider
        public static DataTable CreateDataTable(String CsvPath, bool hasHeaders)
        {
            string inputString = "";
            try
            {
                // check that the file exists before opening it
                if (File.Exists(CsvPath))
                {
                    StreamReader sr = new StreamReader(CsvPath);
                    inputString = sr.ReadToEnd();
                    sr.Close();
                }

                return ParseCsv(inputString, hasHeaders);
                //DataTable myDataTable;
                //OleDbConnection conn;
                //string sql, fileConnection;
                //OleDbDataAdapter oda = new OleDbDataAdapter();
                //fileConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Path.GetDirectoryName(CsvFile) + @"\" + System.IO.Path.GetFileName(CsvFile) + ";Extended Properties=Text;HDR=YES;";;
                //try
                //{
                //    conn = new OleDbConnection();
                //    conn.ConnectionString = fileConnection;
                //    conn.Open();
                //    try
                //    {
                //        sql = String.Format("SELECT * FROM {0}", System.IO.Path.GetFileName(CsvFile));
                //        myDataTable = Utility.CreateDataTable(sql, fileConnection);
                //        if (myDataTable != null && (myDataTable.TableName == string.Empty || myDataTable.TableName == null)) { myDataTable.TableName = System.IO.Path.GetFileNameWithoutExtension(CsvFile); }
                //        return myDataTable;
                //    }
                //    catch (Exception ex)
                //    {
                //        throw new Exception(ex.ToString());
                //    }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
		public static DataSet LoadXMLFileToDataSet(string filePath)
		{
			try
			{
                if (IsExistingFile(filePath) == false) { return null; }
                else
                {
                    DataSet ds = new DataSet();                    
                    ds.ReadXml(filePath);
                    return ds;
                }
			}
			catch(Exception ex){throw new Exception(ex.ToString());}			
		}
        /// <summary>
        /// Loads an XML file to a DataSet based on the XML file chosen in the OpenFileDialog
        /// </summary>
		public static DataSet LoadXMLFileToDataSet()
		{
			try
			{
				OpenFileDialog flDlg = new OpenFileDialog();
				DialogResult dlgResult = flDlg.ShowDialog();
				if(dlgResult == DialogResult.OK)
				{
					DataSet myDS = LoadXMLFileToDataSet(flDlg.FileName);
					return myDS;
				}
				else{ return null;}
			}
			catch(Exception ex)
			{				
				throw new Exception(ex.ToString());
			}			
		}
        /// <summary>
        /// Checks to see if the file exists
        /// </summary>
        /// <param name="filePath">The path to the file</param>
		public static bool IsExistingFile(string filePath)
		{
			try
			{
				if (System.IO.File.Exists(filePath)){return true;}
				else{return false;}
			}
			catch(Exception ex){throw new Exception(ex.ToString());}
		}

        private  static void PatientFormShowDialog()
        {            
            frmPatient.ShowDialog();
        }
		public static void ExportToExcel(System.Data.DataTable table, xl.Worksheet sheet, xl.Range startingRange,int rowOffset, int columnOffset, bool isColumnHeadersExport, string[] xlColumnWidths, string[] xlNumberFormats, bool showPatientForm, string patientFormComment, bool addAutoFilter)
		{
            DataTableExcelExport exportTable = new DataTableExcelExport(table);
            IExportToExcel excelExport = (IExportToExcel)exportTable;
            excelExport.XLApplication = (xl.Application)sheet.Application;
            excelExport.XLWorkbook = (xl.Workbook)sheet.Parent;
            excelExport.XLWorksheet = sheet;
            excelExport.StartingRange = startingRange;
            excelExport.RowOffset = rowOffset;
            excelExport.ColumnOffset = columnOffset;
            excelExport.IsAutoFilter = addAutoFilter;
            exportTable.IsColumnHeadersExported = isColumnHeadersExport;
            exportTable.WorkbookPath = excelExport.XLWorkbook.FullName;
            excelExport.ExportToExcel();      
		}
        public static void ExportToExcel(DataTable table)
        {
            
        }
        public static void ExportToExcel(DataGridView dgv, xl.Worksheet sheet, xl.Range startingRange, int rowOffset, int columnOffset, bool isColumnHeadersIncluded, string[] xlColumnWidths, string[] xlNumberFormats, bool isPatientFormShowing, string patientFormComment, bool addAutoFilter)
        {
            try
            {
                //System.Collections.Specialized.StringCollection list = new System.Collections.Specialized.StringCollection();
                //foreach (DataGridViewColumn dgvc in dgv.Columns)
                //{
                //    if (!dgvc.Visible) list.Add(dgvc.DataPropertyName);
                //}
                ////todo: implement lofic to get the datagridview datasource, clone the table, remove the unvisible columns and export the dgv
                //DataTable dt = ((DataTable)dgv.DataSource).Clone();
                //foreach(string col in list)if(dt.Columns.CanRemove(dt.Columns[col]))dt.Columns.Remove(col);
                Utility.ExportToExcel(Utility.CreateDataTable(dgv), sheet, startingRange, rowOffset, columnOffset, isColumnHeadersIncluded, xlColumnWidths, xlNumberFormats, isPatientFormShowing, patientFormComment, addAutoFilter);
            }
            catch { }
        }

        public static DataTable CreateDataTable(DataView dataView)
        {
            DataTable dt = new DataTable(dataView.Table.TableName);
            DataRow dtRow = null;

            for (int i = 0; i < dataView.Table.Columns.Count; i++)
            {
                dt.Columns.Add(dataView.Table.Columns[i].ColumnName, dataView.Table.Columns[i].DataType);                                
            }

            foreach (DataRowView row in dataView)
            {
                dtRow = dt.NewRow();

                for (int i = 0; i < dataView.Table.Columns.Count; i++)
                {
                    try{
                        dtRow[i] = row[i];
                    }
                    catch{dtRow[i] =DBNull.Value;}

                }

                dt.Rows.Add(dtRow);
            }

            return dt;
        }
        public static DataTable CreateDataTable(System.Data.OleDb.OleDbDataReader dataReader)
        {
            return Utility.CreateDataTable((object) dataReader);
        }
        public static DataTable CreateDataTable(System.Data.SqlClient.SqlDataReader dataReader)
        {
            return Utility.CreateDataTable((object) dataReader);
        }
        public static DataTable CreateDataTable(object dataReader)
        {
            DataRow row;
            System.Collections.IEnumerator enumerator=null;
            if ((dataReader.GetType() != typeof(System.Data.SqlClient.SqlDataReader)) & (dataReader.GetType() != typeof(OleDbDataReader)))
            {
                throw new Exception("Unknown dataReader Type");
            }
//            IdataReader reader = (IdataReader)dataReader;
            DataTable table = new DataTable();
            DataRowCollection rowCollection = (DataRowCollection)LateBinding.LateGet(LateBinding.LateGet(dataReader, null, "GetSchemaTable", new object[0], null, null), null, "Rows", new object[0], null, null);
            try
            {
                enumerator = rowCollection.GetEnumerator();
                while (enumerator.MoveNext())
                {
                    row = (DataRow)enumerator.Current;
                    DataColumn column = new DataColumn();
                    column.ColumnName = row["ColumnName"].ToString();
                    column.Unique = Convert.ToBoolean(RuntimeHelpers.GetObjectValue(row["IsUnique"]));
                    column.AllowDBNull = Convert.ToBoolean(RuntimeHelpers.GetObjectValue(row["AllowDBNull"]));
                    column.ReadOnly = Convert.ToBoolean(RuntimeHelpers.GetObjectValue(row["IsReadOnly"]));
                    column.DataType = Type.GetType(row["DataType"].ToString());
                    table.Columns.Add(column);
                }
            }
            finally
            {
                if (enumerator is IDisposable)
                {
                    ((IDisposable)enumerator).Dispose();
                }
            }
            while (BooleanType.FromObject(LateBinding.LateGet(dataReader, null, "Read", new object[0], null, null)))
            {
                row = table.NewRow();
                foreach (DataColumn column2 in table.Columns)
                {
                    object[] objArray1 = new object[] { column2.ColumnName };
                    row[column2] = RuntimeHelpers.GetObjectValue(LateBinding.LateIndexGet(dataReader, objArray1, null));
                }
                table.Rows.Add(row);
            }
            return table;
        }

 
        /// <summary>
        /// Creates a DataTable based on the current data displayed in the DataGridView
        /// </summary>
        /// <param name="dataGridView">A DataGridView object to populate that DataTable</param>
        public static DataTable CreateDataTable(DataGridView dataGridView)
        {
            try
            {
                object mappingSource = GetDataGridViewDataSource(dataGridView);
                

                DataTable table = new DataTable();
                DataRow dr = null;
                if (mappingSource is DataTable)
                {
                    table.TableName = ((DataTable)mappingSource).TableName;
                    for (int i = 0; i < dataGridView.Columns.Count; i++)
                    {
                        table.Columns.Add(dataGridView.Columns[i].HeaderText, dataGridView.Columns[i].ValueType);
                    }
                    foreach(DataGridViewRow row in dataGridView.Rows)
                    {
                        try
                        {
                            dr = table.NewRow();
                            for (int i = 0; i < row.Cells.Count; i++)
                            {
                                dr[i] = row.Cells[i].Value;
                            }
                            table.Rows.Add(dr);
                        }
                        catch { }
                    }
                }
                return table;
            }
            catch(Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        public static DataTable CreateDataTable(Microsoft.Office.Interop.Excel.Range range, bool isHeadersIncluded, bool autoSetDataTypes)
        {
            try
            {
                return Deloitte.MSExcel.ExcelHelper.LoadRangeToDataTable(range, isHeadersIncluded, autoSetDataTypes, null);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }
        private static object GetDataGridViewDataSource(DataGridView dgv)
        {
            if (dgv.DataSource is DataTable || dgv.DataSource is DataView)
            {
                DataTable table;
                if (dgv.DataSource is DataTable) { table = (DataTable)dgv.DataSource; }
                else { table = ((DataView)dgv.DataSource).Table; }
                return table;
            }       
            else { return null; }
        }

        /// <summary>
        /// Generates a CSV file.
        /// </summary>
        /// <param name="view"></param>
        /// <param name="directoryPath"></param>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static string ExportToCSV(DataGridView view, string directoryPath, string fileName)	
		{
            return Utility.ExportToCSV(Utility.CreateDataTable(view), directoryPath, fileName);
        }
        /// <summary>
		/// Generates a CSV file.
		/// </summary>
		/// <param name="table"></param>
		/// <param name="directoryPath"></param>
		/// <param name="fileName"></param>
		/// <returns>Path to the CSV file</returns>
		public static string ExportToCSV(DataTable table, string directoryPath, string fileName)	
		{
			string fullpath = "";
			if (directoryPath.Substring(directoryPath.Length - 1,1) == @"\" ||directoryPath.Substring(directoryPath.Length - 1,1) == "/")	
			{
				fullpath = directoryPath + fileName;
			}
			else	
			{
				fullpath = directoryPath + @"\" + fileName;
			}
			StreamWriter sw;
			sw=File.CreateText(fullpath);		
				
			StringBuilder oStringBuilder = new StringBuilder();

			
			/*******************************************************************
			 * Start, Creating column header
			 * *****************************************************************/

			foreach(DataColumn oDataColumn in table.Columns)	
			{
				oStringBuilder.Append(oDataColumn.ColumnName + ",");
			}

			sw.WriteLine(oStringBuilder.ToString().Substring(0,oStringBuilder.ToString().Length - 1));
			oStringBuilder.Length = 0;

			/*******************************************************************
			 * End, Creating column header
			 * *****************************************************************/

			/*******************************************************************
			 * Start, Creating rows
			 * *****************************************************************/

			foreach(DataRow oDataRow in table.Rows)	
			{
				
				
				foreach(DataColumn oDataColumn in table.Columns)	
				{
					oStringBuilder.Append(oDataRow[oDataColumn.ColumnName] + ",");
				}
				sw.WriteLine(oStringBuilder.ToString().Substring(0,oStringBuilder.ToString().Length - 1));
				oStringBuilder.Length = 0;

			}
			

			/*******************************************************************
			 * End, Creating rows
			 * *****************************************************************/


			sw.Close();

			return fullpath;

		}
	}    
        
	}

